﻿using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Linq;

namespace StackExchange.DataExplorer.Helpers
{
    public class SqlDialect
    {
        private const string DIALECT_ELEMENT = "dialect";
        private const string DIALECT_KEY = "name";
        private const string DIALECT_QUOTE = "quote";
        private const string QUERY_ELEMENT = "query";
        private const string QUERY_KEY = "name";
        private const string DEFAULT_DIALECT = "mssql";
        private static readonly SqlDialect unknown = new SqlDialect
        {
            Name = "unknown",
            QuoteFormat = "{0}"
        };
        private static readonly Regex mssqlProvider = new Regex(".*\\.SqlClient$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static readonly Regex mysqlProvider = new Regex(".*MySQL.*", RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static readonly Regex oracleProvider = new Regex(".*Oracle.*", RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static readonly Regex pgsqlProvider = new Regex(".*(pgsql|postgres).*", RegexOptions.Compiled | RegexOptions.IgnoreCase);
        private static Dictionary<string, SqlDialect> dialects = new Dictionary<string, SqlDialect>();

        private Dictionary<string, string> queries = new Dictionary<string, string>();

        public string Name { get; private set; }
        public string QuoteFormat { get; private set; }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="key"></param>
        /// <param name="fallback"></param>
        /// <returns></returns>
        public string GetQuery(string key, string fallback)
        {
            if (!queries.ContainsKey(key))
            {
                return fallback;
            }

            return queries[key];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="identifier"></param>
        /// <returns></returns>
        public string Quote(string identifier)
        {
            return string.Format(QuoteFormat, identifier);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filename"></param>
        public static void Parse(string filename)
        {
            var xdialects = XElement.Load(filename);

            foreach (var xdialect in xdialects.Descendants(DIALECT_ELEMENT))
            {
                var name = xdialect.Attribute(DIALECT_KEY).Value.ToLower();
                var quote = xdialect.Attribute(DIALECT_QUOTE);
                var dialect = dialects[name] = new SqlDialect
                {
                    Name = name,
                    QuoteFormat = quote != null ? quote.Value : "{0}"
                };

                foreach (var xquery in xdialect.Descendants(QUERY_ELEMENT)) 
                {
                    dialect.queries[xquery.Attribute(QUERY_KEY).Value] = xquery.Value.Trim();
                }
            }

            if (!dialects.ContainsKey(DEFAULT_DIALECT))
            {
                dialects[DEFAULT_DIALECT] = new SqlDialect
                {
                    Name = DEFAULT_DIALECT,
                    QuoteFormat = "[{0}]"
                };
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public static string Guess(string providerName)
        {
            if (mssqlProvider.IsMatch(providerName))
                return "mssql";

            if (mysqlProvider.IsMatch(providerName))
                return "mysql";

            if (pgsqlProvider.IsMatch(providerName))
                return "postgres";

            if (oracleProvider.IsMatch(providerName))
                return "oracle";

            return "unknown";
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dialect"></param>
        /// <returns></returns>
        public static SqlDialect GetDialect(string dialect)
        {
            if (!dialects.ContainsKey(dialect))
            {
                return unknown;
            }

            return dialects[dialect];
        }
    }
}